﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using fsw.DBUtility;
using fsw.Common;
using System.Reflection;

namespace fsw.DAL
{
    public partial class Admin_Pic
    {
        private string databaseprefix; //数据库表名前缀
        public Admin_Pic(string _databaseprefix)
        {
            databaseprefix = _databaseprefix;
        }
        #region=====基本操作====
        /// <summary>
        /// 根据id判读是否存在
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool Exists(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from " + databaseprefix + "Admin_Pic");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Int,4)};
            parameters[0].Value = id;
            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }
        /// <summary>
        /// 添加数据
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Add(Model.Admin_Pic model)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder str1 = new StringBuilder();//数据字段
            StringBuilder str2 = new StringBuilder();//数据参数
            //利用反射获得属性的所有公共属性
            PropertyInfo[] pros = model.GetType().GetProperties();
            List<SqlParameter> paras = new List<SqlParameter>();
            strSql.Append("insert into " + databaseprefix + "Admin_Pic(");
            foreach (PropertyInfo pi in pros)
            {
                //如果不是主键则追加sql字符串
                if (!pi.Name.Equals("Id"))
                {
                    //判断属性值是否为空
                    if (pi.GetValue(model, null) != null)
                    {
                        str1.Append(pi.Name + ",");//拼接字段
                        str2.Append("@" + pi.Name + ",");//声明参数
                        paras.Add(new SqlParameter("@" + pi.Name, pi.GetValue(model, null)));//对参数赋值
                    }
                }
            }
            strSql.Append(str1.ToString().Trim(','));
            strSql.Append(") values (");
            strSql.Append(str2.ToString().Trim(','));
            strSql.Append(") ");
            strSql.Append(";select @@IDENTITY;");
            object obj = DbHelperSQL.GetSingle(strSql.ToString(), paras.ToArray());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 更新数据
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool Update(Model.Admin_Pic model)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder str1 = new StringBuilder();
            //利用反射获得属性的所有公共属性
            PropertyInfo[] pros = model.GetType().GetProperties();
            List<SqlParameter> paras = new List<SqlParameter>();
            strSql.Append("update " + databaseprefix + "Admin_Pic set ");
            foreach (PropertyInfo pi in pros)
            {
                //如果不是主键则追加sql字符串
                if (!pi.Name.Equals("Id"))
                {
                    //判断属性值是否为空
                    if (pi.GetValue(model, null) != null)
                    {
                        str1.Append(pi.Name + "=@" + pi.Name + ",");//声明参数
                        paras.Add(new SqlParameter("@" + pi.Name, pi.GetValue(model, null)));//对参数赋值
                    }
                }
            }
            strSql.Append(str1.ToString().Trim(','));
            strSql.Append(" where id=@id ");
            paras.Add(new SqlParameter("@id", model.Id));
            return DbHelperSQL.ExecuteSql(strSql.ToString(), paras.ToArray()) > 0;
        }
        /// <summary>
        /// 更新数据
        /// </summary>
        /// <param name="id"></param>
        /// <param name="strValue"></param>
        /// <returns></returns>
        public bool UpdateField(int id, string strValue)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update " + databaseprefix + "Admin_Pic set " + strValue);
            strSql.Append(" where id=" + id);
            return DbHelperSQL.ExecuteSql(strSql.ToString()) > 0;
        }
        /// <summary>
        /// 删除一条数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool Delete(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from  " + databaseprefix + "Admin_Pic ");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Int,4)};
            parameters[0].Value = id;
            return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters) > 0;
        }
        /// <summary>
        /// 得到一个实体
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Model.Admin_Pic GetModel(int id)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder str1 = new StringBuilder();
            Model.Admin_Pic model = new Model.Admin_Pic();
            //利用反射获得属性的所有公共属性
            PropertyInfo[] pros = model.GetType().GetProperties();
            foreach (PropertyInfo p in pros)
            {
                str1.Append(p.Name + ",");//拼接字段
            }
            strSql.Append("select top 1 " + str1.ToString().Trim(','));
            strSql.Append(" from " + databaseprefix + "Admin_Pic");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Int,4)};
            parameters[0].Value = id;
            DataTable dt = DbHelperSQL.Query(strSql.ToString(), parameters).Tables[0];
            if (dt.Rows.Count > 0)
            {
                return DataRowToModel(dt.Rows[0]);
            }
            else
            {
                return null;
            }
        }
        /// <summary>
        /// 图片复制次数
        /// </summary>
        /// <param name="PicPath"></param>
        /// <param name="PicName"></param>
        /// <returns></returns>
        public string GetCount(string PicPath, string PicName)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(0) from " + databaseprefix + "Admin_Pic");
            strSql.Append(" where PicPath='" + PicPath + "' and PicName='" + PicName + "'");
            string count = Convert.ToString(DbHelperSQL.GetSingle(strSql.ToString()));
            if (string.IsNullOrEmpty(count))
            {
                return string.Empty;
            }
            return count;
        }
        /// <summary>
        /// 图片复制查看次数
        /// </summary>
        /// <param name="PicPath"></param>
        /// <param name="PicName"></param>
        /// <param name="TypeId"></param>
        /// <returns></returns>
        public string GetCount(string PicPath, string PicName, int TypeId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(0) from " + databaseprefix + "Admin_Pic");
            strSql.Append(" where PicPath='" + PicPath + "' and PicName='" + PicName + "' and TypeId=" + TypeId);
            string count = Convert.ToString(DbHelperSQL.GetSingle(strSql.ToString()));
            if (string.IsNullOrEmpty(count))
            {
                return string.Empty;
            }
            return count;
        }
        /// <summary>
        /// 查询查看,复数次数
        /// </summary>
        /// <param name="PicPath"></param>
        /// <param name="PicName"></param>
        /// <returns></returns>
        public string GetTypeidCount(string PicPath, string PicName)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(0) as num from " + databaseprefix + "Admin_Pic");
            strSql.Append(" where PicPath='" + PicPath + "' and PicName='" + PicName + "' and TypeId=1");
            strSql.Append(" UNION ALL ");
            strSql.Append("select count(0) as num from " + databaseprefix + "Admin_Pic");
            strSql.Append(" where PicPath='" + PicPath + "' and PicName='" + PicName + "' and TypeId=2");
            DataTable dt = DbHelperSQL.Query(strSql.ToString()).Tables[0];
            if (dt.Rows.Count > 0)
            {
                return "[" + dt.Rows[0]["num"].ToString() + "," + dt.Rows[1]["num"].ToString() + "]";
            }
            return "[0,0]";
        }
        /// <summary>
        /// 查询查看,复制次数,左工号,右非工号
        /// </summary>
        /// <param name="PicPath"></param>
        /// <param name="PicName"></param>
        /// <returns></returns>
        public string Get_TypeId_Count(string PicPath, string PicName)
        {
            string str1 = "and EXISTS (select Id from " + databaseprefix + "Admin where a.userid =id and PatIndex('%[^0-9]%', UserName) = 0 )";
            string str2 = "and EXISTS (select Id from " + databaseprefix + "Admin where a.userid =id and PatIndex('%[^0-9]%', UserName) > 0 )";
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(0) as num from " + databaseprefix + "Admin_Pic a");
            strSql.Append(" where PicPath='" + PicPath + "' and PicName='" + PicName + "' and TypeId=1" + str1);
            strSql.Append(" UNION ALL ");
            strSql.Append("select count(0) as num from " + databaseprefix + "Admin_Pic a");
            strSql.Append(" where PicPath='" + PicPath + "' and PicName='" + PicName + "' and TypeId=1" + str2);
            strSql.Append(" UNION ALL ");
            strSql.Append("select count(0) as num from " + databaseprefix + "Admin_Pic a");
            strSql.Append(" where PicPath='" + PicPath + "' and PicName='" + PicName + "' and TypeId=2" + str1);
            strSql.Append(" UNION ALL ");
            strSql.Append("select count(0) as num from " + databaseprefix + "Admin_Pic a");
            strSql.Append(" where PicPath='" + PicPath + "' and PicName='" + PicName + "' and TypeId=2" + str2);
            DataTable dt = DbHelperSQL.Query(strSql.ToString()).Tables[0];
            if (dt.Rows.Count > 0)
            {
                return string.Format("[\"{0}\",\"{1}\"]", dt.Rows[0]["num"].ToString() + "/" + dt.Rows[1]["num"].ToString(), dt.Rows[2]["num"].ToString() + "/" + dt.Rows[3]["num"].ToString());
                
            }
            return "[0,0]";
        }
        /// <summary>
        /// 获得数据列表
        /// </summary>
        public List<Model.Admin_Pic> GetList(string strWhere, string filedOrder)
        {
            List<Model.Admin_Pic> modelList = new List<Model.Admin_Pic>();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * FROM " + databaseprefix + "Admin_Pic ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            if (filedOrder.Trim() != "")
            {
                strSql.Append(" order by " + filedOrder);
            }
            DataTable dt = DbHelperSQL.Query(strSql.ToString()).Tables[0];
            if (dt.Rows.Count > 0)
            {
                for (int n = 0; n < dt.Rows.Count; n++)
                {
                    modelList.Add(DataRowToModel(dt.Rows[n]));
                }
            }
            return modelList;
        }
        /// <summary>
        /// 查询所有索引
        /// </summary>
        /// <param name="strWhere"></param>
        /// <param name="filedOrder"></param>
        /// <returns></returns>
        public DataTable GetListTable(string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select Id,PicPath,PicName FROM " + databaseprefix + "Admin_Pic ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            if (filedOrder.Trim() != "")
            {
                strSql.Append(" order by " + filedOrder);
            }
            DataTable dt = DbHelperSQL.Query(strSql.ToString()).Tables[0];
            return dt;
        }
        /// <summary>
        /// 分页数据
        /// </summary>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <param name="strWhere"></param>
        /// <param name="filedOrder"></param>
        /// <param name="recordCount"></param>
        /// <returns></returns>
        public DataSet GetList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select a.Id,a.UserId,b.UserName,b.RealName,a.Pid,c.HosName ,a.PicPath,a.PicName,a.AddDate,DATEADD(S, a.[AddDate] + 8 * 3600, '1970-01-01 00:00:00') AS AddTime FROM " + databaseprefix + "Admin_Pic a LEFT JOIN " + databaseprefix + "Admin b ON a.UserId = b.Id  left join " + databaseprefix + "Project c ON a.pid = c.pid");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
            return DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder));
        }
        /// <summary>
        /// 以项目为本，或以人为本,项目图片使用最多情况(查看,复制)
        /// </summary>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <param name="strWhere"></param>
        /// <param name="filedOrder"></param>
        /// <param name="recordCount"></param>
        /// <returns></returns>
        public DataSet GetListStr(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT T.*,a.HosName FROM (SELECT PicPath ,PicName ,COUNT(Id) AS PicNum ,Pid ,max(AddDate) as AddDate From  " + databaseprefix + "Admin_Pic ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append("GROUP BY  PicPath ,PicName,Pid) T inner join " + databaseprefix + "Project a on T.Pid = a.Pid ");
            recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
            return DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder));
        }
        /// <summary>
        /// 分页数据
        /// </summary>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <param name="strWhere"></param>
        /// <param name="filedOrder"></param>
        /// <param name="recordCount"></param>
        /// <returns></returns>
        public List<Model.Admin_Pic> GetPicList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
        {
            List<Model.Admin_Pic> modelList = new List<Model.Admin_Pic>();
            DataTable dt = GetList(pageSize, pageIndex, strWhere, filedOrder, out recordCount).Tables[0];
            if (dt.Rows.Count > 0)
            {
                for (int n = 0; n < dt.Rows.Count; n++)
                {
                    modelList.Add(DataRowToModel(dt.Rows[n]));
                }
            }
            return modelList;
        }
        #endregion
        #region 扩展功能=======
        /// <summary>
        /// 将对象转换实体
        /// </summary>
        public Model.Admin_Pic DataRowToModel(DataRow row)
        {
            Model.Admin_Pic model = new Model.Admin_Pic();
            if (row != null)
            {
                //利用反射获得属性的所有公共属性
                Type modelType = model.GetType();
                for (int i = 0; i < row.Table.Columns.Count; i++)
                {
                    //查找实体是否存在列表相同的公共属性
                    PropertyInfo proInfo = modelType.GetProperty(row.Table.Columns[i].ColumnName);
                    if (proInfo != null && row[i] != DBNull.Value)
                    {
                        proInfo.SetValue(model, row[i], null);//用索引值设置属性值
                    }
                }
            }
            return model;
        }
        #endregion
    }
}
